-- this script is for sql server and azure sql 

use [enter_db_name_here]
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[fk_Qrtz_Triggers_Qrtz_Job_Details]') and objectproperty(id, n'isforeignkey') = 1)
alter table [dbo].[Qrtz_Triggers] drop constraint fk_Qrtz_Triggers_Qrtz_Job_Details
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[fk_Qrtz_Cron_Triggers_Qrtz_Triggers]') and objectproperty(id, n'isforeignkey') = 1)
alter table [dbo].[Qrtz_Cron_Triggers] drop constraint fk_Qrtz_Cron_Triggers_Qrtz_Triggers
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[fk_Qrtz_Simple_Triggers_Qrtz_Triggers]') and objectproperty(id, n'isforeignkey') = 1)
alter table [dbo].[Qrtz_Simple_Triggers] drop constraint fk_Qrtz_Simple_Triggers_Qrtz_Triggers
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[fk_Qrtz_Simprop_Triggers_Qrtz_Triggers]') and objectproperty(id, n'isforeignkey') = 1)
alter table [dbo].[Qrtz_Simprop_Triggers] drop constraint fk_Qrtz_Simprop_Triggers_Qrtz_Triggers
go

if  exists (select * from sys.foreign_keys where object_id = object_id(n'[dbo].[fk_qrtz_job_listeners_Qrtz_Job_Details]') and parent_object_id = object_id(n'[dbo].[qrtz_job_listeners]'))
alter table [dbo].[qrtz_job_listeners] drop constraint [fk_qrtz_job_listeners_Qrtz_Job_Details]

if  exists (select * from sys.foreign_keys where object_id = object_id(n'[dbo].[fk_qrtz_trigger_listeners_Qrtz_Triggers]') and parent_object_id = object_id(n'[dbo].[qrtz_trigger_listeners]'))
alter table [dbo].[qrtz_trigger_listeners] drop constraint [fk_qrtz_trigger_listeners_Qrtz_Triggers]


if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[Qrtz_Calendars]') and objectproperty(id, n'isusertable') = 1)
drop table [dbo].[Qrtz_Calendars]
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[Qrtz_Cron_Triggers]') and objectproperty(id, n'isusertable') = 1)
drop table [dbo].[Qrtz_Cron_Triggers]
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[Qrtz_Blob_Triggers]') and objectproperty(id, n'isusertable') = 1)
drop table [dbo].[Qrtz_Blob_Triggers]
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[Qrtz_Fired_Triggers]') and objectproperty(id, n'isusertable') = 1)
drop table [dbo].[Qrtz_Fired_Triggers]
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[Qrtz_Paused_Trigger_Grps]') and objectproperty(id, n'isusertable') = 1)
drop table [dbo].[Qrtz_Paused_Trigger_Grps]
go

if  exists (select * from sys.objects where object_id = object_id(n'[dbo].[qrtz_job_listeners]') and type in (n'u'))
drop table [dbo].[qrtz_job_listeners]

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[Qrtz_Scheduler_State]') and objectproperty(id, n'isusertable') = 1)
drop table [dbo].[Qrtz_Scheduler_State]
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[Qrtz_Locks]') and objectproperty(id, n'isusertable') = 1)
drop table [dbo].[Qrtz_Locks]
go
if  exists (select * from sys.objects where object_id = object_id(n'[dbo].[qrtz_trigger_listeners]') and type in (n'u'))
drop table [dbo].[qrtz_trigger_listeners]


if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[Qrtz_Job_Details]') and objectproperty(id, n'isusertable') = 1)
drop table [dbo].[Qrtz_Job_Details]
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[Qrtz_Simple_Triggers]') and objectproperty(id, n'isusertable') = 1)
drop table [dbo].[Qrtz_Simple_Triggers]
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[Qrtz_Simprop_Triggers]') and objectproperty(id, n'isusertable') = 1)
drop table [dbo].Qrtz_Simprop_Triggers
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[Qrtz_Triggers]') and objectproperty(id, n'isusertable') = 1)
drop table [dbo].[Qrtz_Triggers]
go

create table [dbo].[Qrtz_Calendars] (
  [Sched_Name] [nvarchar] (120)  not null ,
  [Calendar_Name] [nvarchar] (200)  not null ,
  [Calendar] [varbinary](max) not null
)
go

create table [dbo].[Qrtz_Cron_Triggers] (
  [Sched_Name] [nvarchar] (120)  not null ,
  [Trigger_Name] [nvarchar] (150)  not null ,
  [Trigger_Group] [nvarchar] (150)  not null ,
  [Cron_Expression] [nvarchar] (120)  not null ,
  [Time_Zone_Id] [nvarchar] (80) 
)
go

create table [dbo].[Qrtz_Fired_Triggers] (
  [Sched_Name] [nvarchar] (120)  not null ,
  [entry_id] [nvarchar] (140)  not null ,
  [Trigger_Name] [nvarchar] (150)  not null ,
  [Trigger_Group] [nvarchar] (150)  not null ,
  [Instance_Name] [nvarchar] (200)  not null ,
  [Fired_Time] [bigint] not null ,
  [Sched_Time] [bigint] not null ,
  [Priority] [integer] not null ,
  [State] [nvarchar] (16)  not null,
  [Job_Name] [nvarchar] (150)  null ,
  [Job_Group] [nvarchar] (150)  null ,
  [Is_Nonconcurrent] bit  null ,
  [Requests_Recovery] bit  null 
)
go

create table [dbo].[Qrtz_Paused_Trigger_Grps] (
  [Sched_Name] [nvarchar] (120)  not null ,
  [Trigger_Group] [nvarchar] (150)  not null 
)
go

create table [dbo].[Qrtz_Scheduler_State] (
  [Sched_Name] [nvarchar] (120)  not null ,
  [Instance_Name] [nvarchar] (200)  not null ,
  [Last_Checkin_Time] [bigint] not null ,
  [Checkin_Interval] [bigint] not null
)
go

create table [dbo].[Qrtz_Locks] (
  [Sched_Name] [nvarchar] (120)  not null ,
  [Lock_Name] [nvarchar] (40)  not null 
)
go

create table [dbo].[Qrtz_Job_Details] (
  [Sched_Name] [nvarchar] (120)  not null ,
  [Job_Name] [nvarchar] (150)  not null ,
  [Job_Group] [nvarchar] (150)  not null ,
  [Description] [nvarchar] (250) null ,
  [Job_Class_Name] [nvarchar] (250)  not null ,
  [Is_Durable] bit  not null ,
  [Is_Nonconcurrent] bit  not null ,
  [Is_Update_Data] bit  not null ,
  [Requests_Recovery] bit  not null ,
  [Job_Data] [varbinary](max) null
)
go

create table [dbo].[Qrtz_Simple_Triggers] (
  [Sched_Name] [nvarchar] (120)  not null ,
  [Trigger_Name] [nvarchar] (150)  not null ,
  [Trigger_Group] [nvarchar] (150)  not null ,
  [Repeat_Count] [integer] not null ,
  [Repeat_Interval] [bigint] not null ,
  [Times_Triggered] [integer] not null
)
go

create table [dbo].[Qrtz_Simprop_Triggers] (
  [Sched_Name] [nvarchar] (120)  not null ,
  [Trigger_Name] [nvarchar] (150)  not null ,
  [Trigger_Group] [nvarchar] (150)  not null ,
  [Str_Prop_1] [nvarchar] (512) null,
  [Str_Prop_2] [nvarchar] (512) null,
  [Str_Prop_3] [nvarchar] (512) null,
  [Int_Prop_1] [int] null,
  [Int_Prop_2] [int] null,
  [Long_Prop_1] [bigint] null,
  [Long_Prop_2] [bigint] null,
  [Dec_Prop_1] [numeric] (13,4) null,
  [Dec_Prop_2] [numeric] (13,4) null,
  [Bool_Prop_1] bit null,
  [Bool_Prop_2] bit null,
  [Time_Zone_Id] [nvarchar] (80) null 
)
go

create table [dbo].[Qrtz_Blob_Triggers] (
  [Sched_Name] [nvarchar] (120)  not null ,
  [Trigger_Name] [nvarchar] (150)  not null ,
  [Trigger_Group] [nvarchar] (150)  not null ,
  [Blob_Data] [varbinary](max) null
)
go

create table [dbo].[Qrtz_Triggers] (
  [Sched_Name] [nvarchar] (120)  not null ,
  [Trigger_Name] [nvarchar] (150)  not null ,
  [Trigger_Group] [nvarchar] (150)  not null ,
  [Job_Name] [nvarchar] (150)  not null ,
  [Job_Group] [nvarchar] (150)  not null ,
  [Description] [nvarchar] (250) null ,
  [Next_Fire_Time] [bigint] null ,
  [Prev_Fire_Time] [bigint] null ,
  [Priority] [integer] null ,
  [Trigger_State] [nvarchar] (16)  not null ,
  [Trigger_Type] [nvarchar] (8)  not null ,
  [Start_Time] [bigint] not null ,
  [End_Time] [bigint] null ,
  [Calendar_Name] [nvarchar] (200)  null ,
  [Misfire_Instr] [integer] null ,
  [Job_Data] [varbinary](max) null
)
go

alter table [dbo].[Qrtz_Calendars] with nocheck add
  constraint [pk_Qrtz_Calendars] primary key  clustered
  (
    [Sched_Name],
    [Calendar_Name]
  ) 
go

alter table [dbo].[Qrtz_Cron_Triggers] with nocheck add
  constraint [pk_Qrtz_Cron_Triggers] primary key  clustered
  (
    [Sched_Name],
    [Trigger_Name],
    [Trigger_Group]
  ) 
go

alter table [dbo].[Qrtz_Fired_Triggers] with nocheck add
  constraint [pk_Qrtz_Fired_Triggers] primary key  clustered
  (
    [Sched_Name],
    [entry_id]
  ) 
go

alter table [dbo].[Qrtz_Paused_Trigger_Grps] with nocheck add
  constraint [pk_Qrtz_Paused_Trigger_Grps] primary key  clustered
  (
    [Sched_Name],
    [Trigger_Group]
  ) 
go

alter table [dbo].[Qrtz_Scheduler_State] with nocheck add
  constraint [pk_Qrtz_Scheduler_State] primary key  clustered
  (
    [Sched_Name],
    [Instance_Name]
  ) 
go

alter table [dbo].[Qrtz_Locks] with nocheck add
  constraint [pk_Qrtz_Locks] primary key  clustered
  (
    [Sched_Name],
    [Lock_Name]
  ) 
go

alter table [dbo].[Qrtz_Job_Details] with nocheck add
  constraint [pk_Qrtz_Job_Details] primary key  clustered
  (
    [Sched_Name],
    [Job_Name],
    [Job_Group]
  ) 
go

alter table [dbo].[Qrtz_Simple_Triggers] with nocheck add
  constraint [pk_Qrtz_Simple_Triggers] primary key  clustered
  (
    [Sched_Name],
    [Trigger_Name],
    [Trigger_Group]
  ) 
go

alter table [dbo].[Qrtz_Simprop_Triggers] with nocheck add
  constraint [pk_Qrtz_Simprop_Triggers] primary key  clustered
  (
    [Sched_Name],
    [Trigger_Name],
    [Trigger_Group]
  ) 
go

alter table [dbo].[Qrtz_Triggers] with nocheck add
  constraint [pk_Qrtz_Triggers] primary key  clustered
  (
    [Sched_Name],
    [Trigger_Name],
    [Trigger_Group]
  ) 
go

alter table [dbo].Qrtz_Blob_Triggers with nocheck add
  constraint [pk_Qrtz_Blob_Triggers] primary key  clustered
  (
    [Sched_Name],
    [Trigger_Name],
    [Trigger_Group]
  ) 
go

alter table [dbo].[Qrtz_Cron_Triggers] add
  constraint [fk_Qrtz_Cron_Triggers_Qrtz_Triggers] foreign key
  (
    [Sched_Name],
    [Trigger_Name],
    [Trigger_Group]
  ) references [dbo].[Qrtz_Triggers] (
    [Sched_Name],
    [Trigger_Name],
    [Trigger_Group]
  ) on delete cascade
go

alter table [dbo].[Qrtz_Simple_Triggers] add
  constraint [fk_Qrtz_Simple_Triggers_Qrtz_Triggers] foreign key
  (
    [Sched_Name],
    [Trigger_Name],
    [Trigger_Group]
  ) references [dbo].[Qrtz_Triggers] (
    [Sched_Name],
    [Trigger_Name],
    [Trigger_Group]
  ) on delete cascade
go

alter table [dbo].[Qrtz_Simprop_Triggers] add
  constraint [fk_Qrtz_Simprop_Triggers_Qrtz_Triggers] foreign key
  (
	[Sched_Name],
    [Trigger_Name],
    [Trigger_Group]
  ) references [dbo].[Qrtz_Triggers] (
    [Sched_Name],
    [Trigger_Name],
    [Trigger_Group]
  ) on delete cascade
go

alter table [dbo].[Qrtz_Triggers] add
  constraint [fk_Qrtz_Triggers_Qrtz_Job_Details] foreign key
  (
    [Sched_Name],
    [Job_Name],
    [Job_Group]
  ) references [dbo].[Qrtz_Job_Details] (
    [Sched_Name],
    [Job_Name],
    [Job_Group]
  )
go

create index idx_qrtz_t_j on Qrtz_Triggers(Sched_Name,Job_Name,Job_Group)
create index idx_qrtz_t_jg on Qrtz_Triggers(Sched_Name,Job_Group)
create index idx_qrtz_t_c on Qrtz_Triggers(Sched_Name,Calendar_Name)
create index idx_qrtz_t_g on Qrtz_Triggers(Sched_Name,Trigger_Group)
create index idx_qrtz_t_state on Qrtz_Triggers(Sched_Name,Trigger_State)
create index idx_qrtz_t_n_state on Qrtz_Triggers(Sched_Name,Trigger_Name,Trigger_Group,Trigger_State)
create index idx_qrtz_t_n_g_state on Qrtz_Triggers(Sched_Name,Trigger_Group,Trigger_State)
create index idx_qrtz_t_next_fire_time on Qrtz_Triggers(Sched_Name,Next_Fire_Time)
create index idx_qrtz_t_nft_st on Qrtz_Triggers(Sched_Name,Trigger_State,Next_Fire_Time)
create index idx_qrtz_t_nft_misfire on Qrtz_Triggers(Sched_Name,Misfire_Instr,Next_Fire_Time)
create index idx_qrtz_t_nft_st_misfire on Qrtz_Triggers(Sched_Name,Misfire_Instr,Next_Fire_Time,Trigger_State)
create index idx_qrtz_t_nft_st_misfire_grp on Qrtz_Triggers(Sched_Name,Misfire_Instr,Next_Fire_Time,Trigger_Group,Trigger_State)

create index idx_qrtz_ft_trig_inst_name on Qrtz_Fired_Triggers(Sched_Name,Instance_Name)
create index idx_qrtz_ft_inst_job_req_rcvry on Qrtz_Fired_Triggers(Sched_Name,Instance_Name,Requests_Recovery)
create index idx_qrtz_ft_j_g on Qrtz_Fired_Triggers(Sched_Name,Job_Name,Job_Group)
create index idx_qrtz_ft_jg on Qrtz_Fired_Triggers(Sched_Name,Job_Group)
create index idx_qrtz_ft_t_g on Qrtz_Fired_Triggers(Sched_Name,Trigger_Name,Trigger_Group)
create index idx_qrtz_ft_tg on Qrtz_Fired_Triggers(Sched_Name,Trigger_Group)
go
